import queue
import time

import pymysql as pymysql
from fake_useragent import UserAgent
from lxml import etree
from selenium import webdriver
from selenium.webdriver import ActionChains
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.by import By

import spiderConfig


class footballFull():
    chrome_path = r'./chromedriver.exe'

    def __init__(self, url):
        chrome_options = Options()
        chrome_options.add_argument('--headless')
        chrome_options.add_argument('--disable-gpu')
        self.q = queue.Queue()
        self.driver = webdriver.Chrome(chrome_options=chrome_options)
        self.base_url = url
        self.__host = spiderConfig.host
        self.__port = spiderConfig.port
        self.__user = spiderConfig.user
        self.__password = spiderConfig.password
        self.__db = spiderConfig.db
        self.__charset = spiderConfig.charset
        self.lists = []
        self.dicts = {}
        self.s = ""
        self.ua = UserAgent()

    def sql_operate(self, lists):
        sql__conn = pymysql.connect(
            host=self.__host,
            port=self.__port,
            user=self.__user,
            password=self.__password,
            db=self.__db,
            charset=self.__charset
        )
        cursor = sql__conn.cursor()
        for single in lists:
            is_update_sql = "select * from tp_game where fx_id = %s"
            is_update = cursor.execute(is_update_sql, single['fx_id'])
            if is_update == 0:
                sql_inert = "replace  into tp_game values(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
                data = [
                    (single['id'], single['game_type'], single['match_name'], single['match_date'], single['home_team'],
                     single['away_team'], single['half_score'], single['final_score'], single['match_status'],
                     single['minutes'],
                     single['fx_id'], single['hc'], single['hc_home'], single['hc_away'], single['ou'], single['ou_o'],
                     single['ou_u'],
                     single['hc_half'], single['hc_home_half'], single['hc_away_half'], single['ou_half'],
                     single['ou_o_half'],
                     single['ou_u_half'], single['style'], single['sort'], single['create_time'], single['update_time'],
                     single['tag'])]
                print(data)
                print('正在插入中')
                cursor.executemany(sql_inert, data)
                sql__conn.commit()
            else:
                update_datas = {
                    'minutes': single['minutes'], 'match_date': single['match_date'],
                    'half_score': single['half_score'],
                    'final_score': single['final_score'],
                    'match_status': single['match_status'], 'hc': single['hc'],
                    'hc_home': single['hc_home'], 'hc_away': single['hc_away'], 'ou': single['ou'],
                    'ou_o': single['ou_o'],
                    'ou_u': single['ou_u'], 'hc_half': single['hc_half'], 'hc_home_half': single['hc_home_half'],
                    'hc_away_half': single['hc_away_half'],
                    'ou_half': single['ou_half'],
                    'ou_o_half': single['ou_o_half'],
                    'ou_u_half': single['ou_u_half'], 'update_time': single['update_time'],
                }

                sql = "update tp_game set"
                update = ''.join(
                    [" {key} = '{values}',".format(key=key, values=values) for key, values in update_datas.items()])
                wheres = " where fx_id = '{}'".format(single['fx_id'])
                updats_sql = sql + update[:-1] + wheres
                cursor.execute(updats_sql)
                print('正在更新{}的数据'.format(single['fx_id']))
                sql__conn.commit()
        cursor.close()
        sql__conn.close()

    def get_html(self):
        try:
            self.driver.implicitly_wait(10)
            self.driver.get(self.base_url)
            self.driver.find_element(By.ID, 'button6').click()
        except Exception:
            self.driver.quit()
            time.sleep(1)
            self.main()
        self.driver.find_element(By.XPATH, '//*[@id="tools"]/ul/li[1]').click()
        self.driver.implicitly_wait(10)
        complete_data = self.driver.page_source
        return complete_data

    # 解析出详情页url
    def parse_detail_url(self, complete_data):
        complete_html = etree.HTML(complete_data)
        total_tr = complete_html.xpath('//tbody/tr[@align="center"]')
        suspend_id = complete_html.xpath('//*[@onmouseout="hiddenSbDetail()"]//@id')
        suspend_window = complete_html.xpath('//*[@id="sbOddsDetail"]/table/tbody/tr')
        window_all_data = complete_html.xpath('//*[@id="sbOddsDetail"]')
        return suspend_id, total_tr, suspend_window, window_all_data

    # 组装数据
    def assemble_data(self, total_tr):
        for tr in total_tr[1:]:
            sid = self.q.get()
            if tr.xpath('td[2]/a/font/text()'):
                match_name = "".join(tr.xpath('td[2]/a/font/text()'))
            else:
                match_name = "".join(tr.xpath('td[2]/text()'))
            # match_status =
            minutes = "".join(tr.xpath('td[4]/text()'))
            if not minutes:
                minutes = "".join(tr.xpath('td[4]/b/text()'))
            if not minutes:
                minutes = "".join(tr.xpath('td[4]/font/text()'))
            home_team = "".join(tr.xpath('td[5]/a[3]/text()'))
            final_score = "".join(tr.xpath('td[6]/text()'))
            away_team = "".join(tr.xpath('td[7]/a[1]/text()'))
            half_score = "".join(tr.xpath('td[8]/span[2]/text()'))
            undefined = "".join(tr.xpath('td[10]/div/text()'))
            undefined2 = "".join(tr.xpath('td[11]/div/text()'))
            undefined3 = "".join(tr.xpath('td[12]/div/text()'))
            create_id = time.time()
            times = str(create_id)[:10]
            tr_id = "".join(tr.xpath('@id'))
            time_day = self.get_times(tr_id)
            if time_day == 0:
                match_date = 0
            else:
                timeArray = time.strptime(time_day, "%Y-%m-%d %H:%M")
                # 转为时间戳
                match_date = int(time.mktime(timeArray))
            self.dicts = {
                "id": 0,
                "game_type": 1,
                "match_name": match_name,
                "match_date": match_date,
                "home_team": home_team,
                "away_team": away_team,
                "half_score": half_score,
                "final_score": final_score,
                "match_status": 0,
                "minutes": minutes,
                "fx_id": sid,
                "hc": "",
                "hc_home": "",
                "hc_away": "",
                "ou": 0,
                "ou_o": "",
                "ou_u": "",
                "hc_half": "",
                "hc_home_half": "",
                "hc_away_half": "",
                "ou_half": 0,
                "ou_o_half": "",
                "ou_u_half": "",
                "style": 0,
                "sort": 1,
                "create_time": times,
                "update_time": times,
                "tag": 0
            }

            if undefined2:
                self.s = sid
                window_all_data = self.get_window_selenium_contorl()
                for window_all_tbody in window_all_data:
                    cont = len(window_all_tbody.xpath('//*[@id="ffOddsDetail"]/tbody/tr[1]/td'))
                    if cont == 3:
                        # 让球，主队赔率
                        hc_home = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[2]/div/text()')
                        if not hc_home:
                            hc_home = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[2]/text()')
                        self.dicts["hc_home"] = "".join(hc_home)
                        # 让球
                        hc = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[3]/div/text()')
                        if not hc:
                            hc = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[3]/text()')
                        self.dicts["hc"] = "".join(hc)
                        # 让球，客队队赔率
                        hc_away = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[4]/div/text()')
                        if not hc_away:
                            hc_away = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[4]/text()')
                        self.dicts["hc_away"] = "".join(hc_away)

                        # 大小球，大球赔率
                        ou_o = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[2]/div/text()')
                        if not hc_home:
                            ou_o = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[2]/text()')
                        self.dicts["ou_o"] = "".join(ou_o)

                        # 大小球
                        ou = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[3]/div/text()')
                        if not ou:
                            ou = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[3]/text()')
                        ou = "".join(ou).replace(" ", '').replace("大", "").replace("小", "").strip()
                        ou = ou.split('/')[0]
                        if not ou:
                            ou = 0
                        self.dicts["ou"] = ou

                        # 大小球，小球赔率
                        ou_u = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[4]/div/text()')
                        if not ou_u:
                            ou_u = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[4]/text()')
                        self.dicts["ou_u"] = "".join(ou_u)

                        # 半场让球，主队赔率【新增】
                        hc_home_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[2]/div/text()')
                        if not hc_home_half:
                            hc_home_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[2]/text()')
                        self.dicts["hc_home_half"] = "".join(hc_home_half)

                        # 半场让球【新增】
                        hc_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[3]/div/text()')
                        if not hc_half:
                            hc_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[3]/text()')
                        self.dicts["hc_half"] = "".join(hc_half)

                        # 半场让球【新增】
                        hc_away_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[4]/div/text()')
                        if not hc_away_half:
                            hc_away_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[4]/text()')
                        self.dicts["hc_away_half"] = "".join(hc_away_half)

                        # 半场大小球，大球赔率【新增】
                        ou_o_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[2]/div/text()')
                        if not ou_o_half:
                            ou_o_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[2]/text()')
                        self.dicts["ou_o_half"] = "".join(ou_o_half)

                        # 半场大小球【新增】
                        ou_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[3]/div/text()')
                        if not ou_half:
                            ou_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[3]/text()')
                        ou_half = "".join(ou_half).strip().split('/')[0]
                        if not ou_half:
                            ou_half = 0
                        self.dicts["ou_half"] = ou_half

                        # 半场大小球，小球赔率【新增】
                        ou_u_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[4]/div/text()')
                        if not ou_u_half:
                            ou_u_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[4]/text()')
                        self.dicts["ou_u_half"] = "".join(ou_u_half)
                        # print(match_name, hc_home, hc, hc_away, ou_o,
                        #       ou, ou_u, hc_home_half, hc_half, hc_away_half, ou_o_half,
                        #       ou_half, ou_u_half)
                    if cont == 4:
                        # 让球，主队赔率
                        hc_home = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[5]/div/text()')
                        if not hc_home:
                            hc_home = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[5]/text()')
                        self.dicts["hc_home"] = "".join(hc_home)
                        # 让球
                        hc = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[6]/div/text()')
                        if not hc:
                            hc = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[6]/text()')
                        self.dicts["hc"] = "".join(hc)

                        # 让球，客队队赔率
                        hc_away = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[7]/div/text()')
                        if not hc_away:
                            hc_away = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[2]/td[7]/text()')
                        self.dicts["hc_away"] = "".join(hc_away)

                        # 大小球，大球赔率
                        ou_o = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[5]/div/text()')
                        if not ou_o:
                            ou_o = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[5]/text()')
                        self.dicts["ou_o"] = "".join(ou_o)

                        # 大小球
                        ou = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[6]/div/text()')
                        if not ou:
                            ou = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[6]/text()')
                        ou = "".join(ou).replace(" ", '').replace("大", "").replace("小", "").strip()
                        ou = ou.split('/')[0]
                        if not ou:
                            ou = 0
                        self.dicts["ou"] = ou

                        # 大小球，小球赔率
                        ou_u = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[7]/div/text()')
                        if not ou_u:
                            ou_u = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[2]/tbody/tr[4]/td[7]/text()')
                        self.dicts["ou_u"] = "".join(ou_u)

                        # 半场让球，主队赔率【新增】
                        hc_home_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[5]/div/text()')
                        if not hc_home_half:
                            hc_home_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[5]/text()')
                        self.dicts["hc_home_half"] = "".join(hc_home_half)

                        # 半场让球【新增】
                        hc_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[6]/div/text()')
                        if not hc_half:
                            hc_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[6]/text()')
                        self.dicts["hc_half"] = "".join(hc_half)

                        # 半场让球【新增】
                        hc_away_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[2]/td[7]/div/text()')
                        if not hc_away_half:
                            hc_away_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[7]/td[4]/text()')
                        self.dicts["hc_away_half"] = "".join(hc_away_half)

                        # 半场大小球，大球赔率【新增】
                        ou_o_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[2]/div/text()')
                        if not ou_o_half:
                            ou_o_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[2]/text()')
                        self.dicts["ou_o_half"] = "".join(ou_o_half)

                        # 半场大小球【新增】
                        ou_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[6]/div/text()')
                        if not ou_half:
                            ou_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[6]/text()')
                        ou_half = "".join(ou_half).strip().split('/')[0]
                        if not ou_half:
                            ou_half = 0
                        self.dicts["ou_half"] = ou_half

                        # 半场大小球，小球赔率【新增】
                        ou_u_half = window_all_tbody.xpath(
                            '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[7]/div/text()')
                        if not ou_u_half:
                            ou_u_half = window_all_tbody.xpath(
                                '//*[@id="sbOddsDetail"]/table[4]/tbody/tr[4]/td[7]/text()')
                        self.dicts["ou_u_half"] = "".join(ou_u_half)
            else:
                self.dicts["hc_home"] = ""
                self.dicts["hc"] = ""
                self.dicts["hc_away"] = ""
                self.dicts["ou_o"] = ""
                self.dicts["ou"] = 0
                self.dicts["ou_u"] = ""
                self.dicts["hc_home_half"] = ""
                self.dicts["hc_half"] = ""
                self.dicts["hc_away_half"] = ""
                self.dicts["ou_o_half"] = ""
                self.dicts["ou_half"] = 0
                self.dicts["ou_u_half"] = ""
            self.lists.append(self.dicts)
            print(self.dicts)
        self.driver.close()
        return self.lists

    def get_times(self, tr_id):
        global is_windows
        self.driver.implicitly_wait(10)
        tr_id_xpath = '//*[@id="{}"]/td[14]/a[3]'.format(tr_id)
        try:
            if tr_id_xpath:
                self.driver.find_element(By.XPATH, tr_id_xpath).click()
                self.driver.implicitly_wait(10)
                windows_all = self.driver.window_handles
                if len(windows_all) == 2:
                    is_windows = 2
                    self.driver.switch_to.window(self.driver.window_handles[1])
                    self.driver.implicitly_wait(10)
                    time_day_list = self.driver.find_element(By.XPATH, '//*[@class="row"]').text
                    time_day_list = time_day_list.split()
                    time_day = "{} {}".format(time_day_list[1], time_day_list[2])
                    self.driver.close()
                    self.driver.switch_to.window(self.driver.window_handles[0])
                    return time_day
            return 0
        except Exception as e:
            print("操作太频繁了，请先歇一歇。")
            if is_windows == 2:
                self.driver.close()
            time.sleep(1)
            self.driver.switch_to.window(self.driver.window_handles[0])
            return 0

    # selenium浮窗数据
    def get_window_selenium_contorl(self):
        try:
            s_id = self.driver.find_element(By.ID, self.s)
            ActionChains(self.driver).move_to_element(s_id).perform()
            self.driver.implicitly_wait(10)
            complete_data = self.driver.page_source
            suspend_id, total_tr, suspend_window, window_all_data = self.parse_detail_url(complete_data)
            return window_all_data
        except Exception:
            print("网速堪忧")
            complete_data = self.driver.page_source
            suspend_id, total_tr, suspend_window, window_all_data = self.parse_detail_url(complete_data)
            return window_all_data

    def main(self):
        complete_data = self.get_html()
        suspend_id, total_tr, suspend_window, window_all_data = self.parse_detail_url(complete_data)
        for sid in suspend_id:
            self.q.put(sid)
        lists = self.assemble_data(total_tr)
        self.sql_operate(lists)


if __name__ == '__main__':
    run = footballFull('http://live.titan007.com/oldIndexall.aspx')
    run.main()
